package com.tingyun.sign.core.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.tingyun.sign.core.entity.ScActivityBean;

@Repository("activityDao")
public interface ActivityDao extends PagingAndSortingRepository<ScActivityBean, Integer>, JpaSpecificationExecutor<ScActivityBean> {
	
	@Query(value = "select count(t.id) from ScActivityBean t where t.ctime >=:start and t.ctime <:end and t.area = :type")
	public Integer countByType(@Param("type") Integer type, @Param("start") Date start, @Param("end") Date end);

	@Query(value = "select count(t.id) from ScActivityBean t where t.ctime >=:start and t.ctime <:end ")
	public Integer countEffectiveAcs(@Param("start")Date start, @Param("end")Date end);

	@Query(value = "select count(t.id) from ScActivityBean t where t.topic = :topic and t.area = :area and t.status=0")
	public Integer findByTopic(@Param("topic")String topic , @Param("area")Integer area);
	
	@Query(value = "select count(t.id) from ScActivityBean t where t.topic = :topic and t.area=:area and t.status=0 ")
	public Integer findByTopicAndArea(@Param("topic")String topic,@Param("area")Integer area);
	
	@Query(value = "select count(t.id) from ScActivityBean t where t.topic = :topic and t.id!=:id and t.status=0")
	public Integer findByTopicAndId(@Param("id")Long id,@Param("topic")String topic);

	@Modifying
	@Query(value = "update ScActivityBean set topic = :topic, area = :area,remark = :remark, muser = :muser, mtime=sysdate where id=:id and status=0")
	public void updateById(@Param("id")Long id, @Param("area")Integer area, @Param("topic")String topic, @Param("remark")String remark, @Param("muser")Long muser);

	@Modifying
	@Query(value = "update ScActivityBean set status = 1, muser = :muser, mtime=sysdate where id=:id and status=0")
	public void deleteById(@Param("id")Long id, @Param("muser")Long muser);

	@Query(value = "select t from ScActivityBean t where t.status=0 and t.area = :area order by id desc")
	public List<ScActivityBean> findAllActivities(@Param("area")Integer area);
	
	@Query(value = "select t from ScActivityBean t where t.status=0 order by id desc")
	public List<ScActivityBean> findAllActivities();

	@Query(value = "select t from ScActivityBean t where t.id in (:acIds) order by id desc")
	public List<ScActivityBean> findByIds(@Param("acIds")List<Long> acIds);
	
	@Query(value = "select count(t.id) from ScActivityBean t where t.ctime >=:start and t.ctime <:end and t.area = :type and t.status = :status")
	public Integer countByTypeByStatus(@Param("type") Integer type, @Param("start") Date start, @Param("end") Date end,@Param("status")Integer status);
	
	@Query(value = "select count(t.id) from ScActivityBean t where t.ctime >=:start and t.ctime <:end and t.status = :status")
	public Integer countEffectiveAcsByStatus(@Param("start")Date start, @Param("end")Date end,@Param("status")Integer status);

}
